package com.pai.dao.AppUser;

import com.pai.dao.BaseDao;
import com.pai.entity.AppUser;
import com.pai.entity.vmodel.UserInfo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class AppUserDaoImpl implements AppUserDao {
    @Override
    public List<AppUser> getList(Connection connection) throws Exception {
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        List<AppUser> resultList = new ArrayList<AppUser>();

        StringBuffer sql = new StringBuffer();
        //没有查询条件限制下的原版查询语句
        sql.append("select * from appuser, approle where appRoleId = approle.id");
        List<Object> params = new ArrayList<Object>();


        System.out.println(sql);
        rs = BaseDao.execute(connection, preparedStatement, rs, sql.toString(), params.toArray());

        while (rs.next()){
            AppUser _itme = new AppUser();
            _itme.setId(rs.getInt("id"));
            _itme.setAccount(rs.getString("account"));
            _itme.setPassword(rs.getString("password"));
            _itme.setAppRoleId(rs.getInt("appRoleId"));
            _itme.setAppRoleName(rs.getString("roleName"));
            resultList.add(_itme);
        }
        BaseDao.closeResource(null,preparedStatement,rs);
        return resultList;
    }

    /**
     * 获取项详情
     * @param connection
     * @param id
     * @return
     * @throws Exception
     */
    @Override
    public AppUser getItemDetail(Connection connection, String id) throws Exception {
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        AppUser item = new AppUser();

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();
        //没有查询条件限制下的原版查询语句
        sql.append("select * from appuser, approle  where id = ? and appRoleId = approle.id");
        params.add(id);

        System.out.println(sql);
        rs = BaseDao.execute(connection, preparedStatement, rs, sql.toString(), params.toArray());

        while (rs.next()){
            AppUser _itme = new AppUser();
            _itme.setId(rs.getInt("id"));
            _itme.setAccount(rs.getString("account"));
            _itme.setPassword(rs.getString("password"));
            _itme.setAppRoleId(rs.getInt("appRoleId"));
            _itme.setAppRoleName(rs.getString("roleName"));
            item = _itme;
        }
        BaseDao.closeResource(null, preparedStatement, rs);
        return item;
    }
    /**
     * 新增
     * @param connection
     * @param item
     * @return
     * @throws Exception
     */
    @Override
    public int add(Connection connection, AppUser item) throws Exception {
        PreparedStatement preparedStatement = null;
        int rs = 0;

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();

        sql.append("insert into appuser (account, password, appRoleId) values (?, ?, ?)");
        params.add(item.getAccount());
        params.add(item.getPassword());
        params.add(item.getAppRoleId());

        System.out.println(sql);
        rs = BaseDao.execute(connection, preparedStatement, sql.toString(), params.toArray());
        BaseDao.closeResource(null, preparedStatement, null);
        if(rs == 0) return rs;

        //再次查询数据库，返回新增数据的id值
        else {
            PreparedStatement _preparedStatement = null;
            ResultSet _rs = null;
            int resultId = 0;

            StringBuffer _sql = new StringBuffer();
            List<Object> _params = new ArrayList<Object>();
            //没有查询条件限制下的原版查询语句
            _sql.append("select id from appuser where account = ? and password = ?");
            _params.add(item.getAccount());
            _params.add(item.getPassword());

            System.out.println(sql);
            _rs = BaseDao.execute(connection, _preparedStatement, _rs, _sql.toString(), _params.toArray());

            while (_rs.next()){
                resultId = _rs.getInt("id");
            }
            BaseDao.closeResource(null, preparedStatement, _rs);
            return  resultId;  //返回新增的id

        }





    }



    /**
     * 更新
     * @param connection
     * @param item
     * @return
     * @throws Exception
     */
    @Override
    public int update(Connection connection, AppUser item) throws Exception {
        PreparedStatement preparedStatement = null;
        int rs = 0;

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();

        sql.append("update appuser set account = ?, password = ?, appRoleId = ? where id = ?");
        params.add(item.getAccount());
        params.add(item.getPassword());
        params.add(item.getAppRoleId());
        params.add(item.getId());

        System.out.println(sql);
        rs = BaseDao.execute(connection, preparedStatement, sql.toString(), params.toArray());

        BaseDao.closeResource(null, preparedStatement, null);
        return rs;
    }

    /**
     * 更新用户角色
     * @param connection
     * @param personnelId  用户id
     * @param roleName  要更新为什么角色
     * @return
     * @throws Exception
     */
    @Override
    public int updateRole(Connection connection, String personnelId, String roleName) throws Exception {
        PreparedStatement preparedStatement = null;
        int rs = 0;

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();

        sql.append("update appuser set appRoleId = (select id from approle where roleName = ? ) where id = (select accountId from personnel where personnel.id = ?)");
        params.add(roleName);
        params.add(personnelId);

        System.out.println(sql);
        rs = BaseDao.execute(connection, preparedStatement, sql.toString(), params.toArray());

        BaseDao.closeResource(null, preparedStatement, null);
        return rs;
    }

    /**
     * 更新系统用户账号
     * @param connection
     * @param id  系统用户id主键
     * @param newAccount  新账号
     * @return
     * @throws Exception
     */
    @Override
    public int updateAccount(Connection connection, String id, String newAccount) throws Exception {
        PreparedStatement preparedStatement = null;
        int rs = 0;

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();

        sql.append("update appuser set account = ? where id = ?");
        params.add(newAccount);
        params.add(id);

        System.out.println(sql);
        rs = BaseDao.execute(connection, preparedStatement, sql.toString(), params.toArray());

        BaseDao.closeResource(null, preparedStatement, null);
        return rs;
    }

    /**
     * 删除
     * @param connection
     * @param id
     * @return
     * @throws Exception
     */
    @Override
    public int delete(Connection connection, String id) throws Exception {
        PreparedStatement preparedStatement = null;
        int rs = 0;

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();

        sql.append("delete from appuser where id = ?");
        params.add(id);


        System.out.println(sql);
        rs = BaseDao.execute(connection, preparedStatement, sql.toString(), params.toArray());

        BaseDao.closeResource(null, preparedStatement, null);
        return rs;
    }

    /**
     * 登录
     * @param connection
     * @param appUser
     * @return
     * @throws Exception
     */
    @Override
    public AppUser login(Connection connection, AppUser appUser) throws Exception {
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        AppUser item = null;

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();
        //没有查询条件限制下的原版查询语句
        sql.append("select * from appuser where account = ? and password = ?");
        params.add(appUser.getAccount());
        params.add(appUser.getPassword());

        System.out.println(sql);
        rs = BaseDao.execute(connection, preparedStatement, rs, sql.toString(), params.toArray());

        while (rs.next()){
            AppUser _itme = new AppUser();
            _itme.setId(rs.getInt("id"));
            _itme.setAccount(rs.getString("account"));
            _itme.setPassword(rs.getString("password"));
            _itme.setAppRoleId(rs.getInt("appRoleId"));
            item = _itme;
        }
        BaseDao.closeResource(null, preparedStatement, rs);
        return item;
    }

    /**
     * 根据用户账号获取用户信息
     * @param connection
     * @param account 账号
     * @return
     * @throws Exception
     */
    @Override
    public UserInfo getUserInfo(Connection connection, String account) throws Exception {
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        UserInfo item = null;

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();
        //没有查询条件限制下的原版查询语句
        sql.append("select * from appuser, approle, department, personnel where account = ? and appuser.appRoleId = approle.id and personnel.accountId = appuser.id and personnel.departmentId = department.id");
        params.add(account);

        System.out.println(sql);
        rs = BaseDao.execute(connection, preparedStatement, rs, sql.toString(), params.toArray());

        while (rs.next()){
            UserInfo _itme = new UserInfo();
            _itme.setPersonnelId(rs.getInt("personnel.id"));
            _itme.setUserName(rs.getString("personnel.name"));
            _itme.setDepartmentId(rs.getInt("department.id"));
            _itme.setDepartmentName(rs.getString("department.depName"));
            _itme.setRolesId(rs.getInt("approle.id"));
            String rolesName = rs.getString("approle.roleName");
            _itme.setRoles(new String[]{rolesName});
            _itme.setAppuserId(rs.getInt("appuser.id"));
            _itme.setAppuserAccount(rs.getString("appuser.account"));
            item = _itme;
        }
        BaseDao.closeResource(null, preparedStatement, rs);
        return item;
    }
}
